from tkinter import *
from tkinter import filedialog
from tkinter import ttk
from tkinter import font as tkfont
from threading import Thread
from mini_tools.cjjtool import *
from mini_tools.myexcel import *
from mini_tools.mytools import *
from mini_tools.uitool import *
from sql import sqlseverDB
from config import *
import tkinter.messagebox as messagebox
import time

class ShopeeADKW2Fra(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        Label(self, text='虾皮采集关联词功能', fg="black", bg="yellow",font=("微软雅黑", 13)).pack(fill=X,padx=2)
        ExpandGLKW(self).pack(fill=X,padx=3,pady=3)
        #ExpandZDGLKW(self).pack(fill=X,padx=3,pady=3)
        


class ExpandGLKW(Frame):

    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)

        self.ppdStr1=StringVar(value='正在获取...')
        self.ppdStr2=StringVar(value='正在获取任务状态...')
        self.ppdStr3=StringVar(value='正在获取...')
        self.createWidgets()
        Thread(target=self.get_pp_count).start()
        Thread(target=self.get_tt_count).start()
        Thread(target=self.get_task_state).start()

    def createWidgets(self):
        f1=Frame(self)
        f1.pack(fill=X)

        f0=Frame(self)
        f0.pack(fill=X)
        f01=Frame(f0)
        f01.pack(fill=X)
        f02=Frame(f0)
        f02.pack(fill=X)
        
        self.btn_start_task=Button(f02,text=f'采集出单词/精准词(CTR>=10%)的关联词',command=lambda:Thread(target=self.start_task).start(),fg='#ffffff',bg='green',state=DISABLED)
        self.btn_start_task.pack(side=LEFT,padx=2)
        Label(f02, textvariable=self.ppdStr2,fg='#871F78',anchor="w").pack(side=LEFT,padx=2, pady=2,fill=X)


        f05=Frame(f0)
        f05.pack(fill=X,pady=2)
        self.btn_glcj_ppd=Button(f05,text=f'采集关联词的匹配度',command=lambda:Thread(target=self.cj_ppd).start(),fg='#ffffff',bg='green',state=DISABLED)
        self.btn_glcj_ppd.pack(side=LEFT,padx=2)
        Label(f05, textvariable=self.ppdStr3,fg='#871F78',anchor="w").pack(side=LEFT,padx=2, pady=2,fill=X)


        f07=Frame(f0)
        f07.pack(fill=X,pady=2)
        self.btn_to_YNADSCV=Button(f07,text='将匹配度符合的长尾词添加到精准匹配词表',command=lambda:Thread(target=self.to_ynadcsv).start(),fg='#ffffff',bg='green')
        self.btn_to_YNADSCV.pack(side=LEFT,padx=2)
        Label(f07, textvariable=self.ppdStr1,fg='#871F78',anchor="w").pack(side=LEFT,padx=2, pady=2,fill=X)



    def get_pp_count(self):
        while True:
            sqlstr='''select 
                        count(case when deepseek='高度匹配' and source_kw_type = '关联词' then 1 end),
                        count(case when deepseek='高度匹配' and source_kw_type = '关联词' and is_add=1 then 1 end),
                        count(case when deepseek='比较匹配' and source_kw_type = '关联词' then 1 end),
                        count(case when deepseek='比较匹配' and source_kw_type = '关联词' and is_add=1 then 1 end)
                        from [ShopeeeADTZKW]
                '''
            rs1=self.ms.ExecQuery(sqlstr)
            ttt=rs1[0]
            self.ppdStr1.set(f'在用广告出单词关联词,高度匹配总数:{ttt[0]},已添加为精准词:{ttt[1]};比较匹配总数:{ttt[2]},已添加为精准词:{ttt[3]}')
            time.sleep(10)
    def get_tt_count(self):

        while True:
            sqlstr='''select 

                        count(case when deepseek is not null and source_kw_type = '关联词' then 1 end),
                        count(case when deepseek is null and source_kw_type = '关联词' then 1 end)
                        from [ShopeeeADTZKW]
                '''
            rs1=self.ms.ExecQuery(sqlstr)
            ttt=rs1[0]
            self.ppdStr3.set(f'已采集:{ttt[0]},未采集:{ttt[1]}')

            time.sleep(10)
    
    def get_task_state(self):
        while True:
            rs= self.ms.ExecQuery('select id,state,tcount from task where id in (44,45)')
            
            for tid, tstate,tcount in rs:
                cbtn=self.btn_start_task if tid==45 else self.btn_glcj_ppd
                if tstate==0:
                    if tid==45:
                        sqlstr='''select 
                                    count(case when is_tz=0 and update_time>= CAST(GETDATE() AS DATE) then 1  end),
                                    count(case when is_tz=1 then 1 end)
                                from ShopeeADKWInfo where (conversion>0 or CTR>=0.10) 
                                and deepseek ='高度匹配' '''
                        rs_ttt=self.ms.ExecQuery(sqlstr)
                        need_cj,has_cj=rs_ttt[0]
                        self.ppdStr2.set(f'任务未开启,关联词已采集:{has_cj},需采集:{need_cj}')
                    cbtn.config(state=NORMAL)
                else:
                    if tid==45:
                        self.ppdStr2.set(f'正在运行,店铺剩余需运行:{tcount}')
                    cbtn.config(state=DISABLED)
            time.sleep(10)


    def start_task(self):
        
        
        cbtn=self.btn_start_task
        cbtn.config(state=DISABLED)

        task_name='采集关联词'

        rs=self.ms.ExecQuery('select count(*) from houtai where isAddKw=1 and is_task_baned=0')

        aff= self.ms.ExecNoQuery('update task set state=1,tcount=? where id=45',(rs[0][0]))
        if aff>0:
            print(f'任务《{task_name}》启动成功,待采集数:{rs[0][0]}')
        else:
            print(f'任务《{task_name}》启动失败')

    
    def cj_ppd(self):

        cbtn=self.btn_glcj_ppd
        stype='关联词'
        task_name='采集关联词拓展词匹配度'
        task_id=44
        cbtn.config(state=DISABLED)

        
        rs=self.ms.ExecQuery(f"select count(*) from [ShopeeeADTZKW] where deepseek is null and source_kw_type = '{stype}' ")
        if rs[0][0]==0:
            print(f'当前表《ShopeeeADTZKW》{stype}待无匹配度待采集')
        else:

            aff= self.ms.ExecNoQuery('update task set state=1,tcount=? where id=?',(rs[0][0],task_id))
            if aff>0:
                print(f'任务《{task_name}》启动成功,待采集数:{rs[0][0]}')
            else:
                print(f'任务《{task_name}》启动失败')

    def to_ynadcsv(self):
       
        cbtn=self.btn_to_YNADSCV

        cbtn.config(state=DISABLED)

        sql_str=f'''
            select * from ShopeeeADTZKW as s left join campaign as c on s.ProductID=c.product_id
            where s.source_kw_type='关联词'
            and s.is_add=0
            and
            ( ((LEN(TRIM(s.source_kw)) - LEN(REPLACE(TRIM(s.source_kw), ' ', '')) + 1)=1 and s.deepseek='高度匹配' )
                or
                ((LEN(TRIM(s.source_kw)) - LEN(REPLACE(TRIM(s.source_kw), ' ', '')) + 1)>=2 and s.deepseek in ('比较匹配','高度匹配') )
            )
            '''
        rs=self.ms.ExecQuerydict(sql_str)
        print(f'共获取到 {len(rs)} 个可导入长尾词,开始导入...')
        cur_time=datetime.now()
        j=1
        suc=0
        fail=0
        ig_n=0
        for r in rs:

            kw_form='新拓展词表关联词'
            
            pid=r['ProductID']
            kw=r['kw']
            rs1=self.ms.ExecQuery('select count(*) from YN_AD_CSV where Product_ID=? and Search_Query=?',(pid,kw))

            if rs1[0][0]==0:

                pdatas={
                    'BrowserID':r['bid'],
                    'DpName':r['bname'],
                    'GroupName':r['gname'],
                    'User_Name':r['UserName'],
                    'Shop_ID':r['shopid'],
                    'Product_Name_Ad_Name':r['PTitle'],
                    'Product_ID':pid,
                    'Search_Query':kw,
                    'Search_Volume':r['SearchCount'],
                    'uploadTime':cur_time,
                    'updateTime':cur_time,
                    'WordNum':r['WordNum'],
                    'deepseek':r['deepseek'],
                    'kwfrom':kw_form
                }
                zdstr=','.join(pdatas.keys())
                params=list(pdatas.values())
                wstr=','.join(['?' for _ in params])
                aff=self.ms.ExecNoQuery(f'insert into YN_AD_CSV({zdstr}) values({wstr})',params)
                if aff>0:
                    suc+=1
                    aff2=self.ms.ExecNoQuery('update ShopeeeADTZKW set update_time=?,is_add=1 where ProductID=? and kw=?',(cur_time,pid,kw))
                    print(f'第{j}个拓展词:《{kw}》,添加到《YN_AD_CSV》精准词表成功,标记状态:{aff2},任务剩余:{len(rs)-j}')
                else:
                    fail+=1
                    print(f'第{j}个拓展词:《{kw}》,添加到《YN_AD_CSV》精准词表失败,任务剩余:{len(rs)-j}')
            else:
                ig_n+=1
                aff2=self.ms.ExecNoQuery('update ShopeeeADTZKW set update_time=?,is_add=1 where ProductID=? and kw=?',(cur_time,pid,kw))
                print(f'第{j}个拓展词:《{kw}》,《YN_AD_CSV》精准词表已存在该词忽略,标记状态:{aff2}任务剩余:{len(rs)-j}')

            j+=1

        print(f'本次需导入{len(rs)}个数据到,导入精准词表,其中成功:{suc},失败:{fail},已存在忽略:{ig_n}')
        messagebox.showinfo('提示',f'本次需导入{len(rs)}个数据到,导入精准词表,其中成功:{suc},失败:{fail},已存在忽略:{ig_n}')
        cbtn.config(state=NORMAL)

class ExpandZDGLKW(Frame):

    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)

        self.ppdStr1=StringVar(value='正在获取...')
        self.ppdStr2=StringVar(value='正在获取任务状态...')
        self.ppdStr3=StringVar(value='正在获取...')
        self.GlStr1=StringVar(value='正在获取...')
        self.createWidgets()
        Thread(target=self.get_pp_count).start()
        Thread(target=self.get_tt_count).start()
        Thread(target=self.get_task_state).start()

    def createWidgets(self):

        ttk.Separator(self,orient='horizontal').pack(fill=X,pady=2)
        f0=Frame(self)
        f0.pack(fill=X)
        f01=Frame(f0)
        f01.pack(fill=X,pady=2)

    
        label_font = tkfont.Font(family="微软雅黑", size=10, weight="bold")
        Label(f01, text='输入单个或者多个词/词组(一行一个词/词组):',font=label_font).pack(side=LEFT, padx=2)

        f02=Frame(f0)
        f02.pack(fill=X,pady=2)
        self.KWIpt = Text(f02,height=5,width=50)
        self.KWIpt.pack(side=LEFT)
        Label(f02, text='PID:',font=label_font).pack(side=LEFT, padx=2)
        self.PIDIpt=Entry(f02)
        self.PIDIpt.pack(side=LEFT, padx=2)


        f03=Frame(f0)
        f03.pack(fill=X)
        self.btn_add_onekw=Button(f03,text=f'手动添加需采集关联词',command=lambda:Thread(target=self.add_onekw).start(),fg='#ffffff',bg='green')
        self.btn_add_onekw.pack(side=LEFT,padx=2)

        f04=Frame(f0)
        f04.pack(fill=X,pady=2)
        self.btn_start_task=Button(f04,text=f'采集手动添加关联词',command=lambda:Thread(target=self.start_task).start(),fg='#ffffff',bg='green',state=DISABLED)
        self.btn_start_task.pack(side=LEFT,padx=2)
        Label(f04, textvariable=self.GlStr1,fg='#871F78',anchor="w").pack(side=LEFT,padx=2, pady=2,fill=X)


        f05=Frame(f0)
        f05.pack(fill=X,pady=2)
        self.btn_glcj_ppd=Button(f05,text=f'采集指定关联词的匹配度',command=lambda:Thread(target=self.cj_ppd).start(),fg='#ffffff',bg='green',state=DISABLED)
        self.btn_glcj_ppd.pack(side=LEFT,padx=2)
        Label(f05, textvariable=self.ppdStr3,fg='#871F78',anchor="w").pack(side=LEFT,padx=2, pady=2,fill=X)


        f07=Frame(f0)
        f07.pack(fill=X,pady=2)
        self.btn_to_YNADSCV=Button(f07,text='将匹配度符合的长尾词添加到精准匹配词表',command=lambda:Thread(target=self.to_ynadcsv).start(),fg='#ffffff',bg='green')
        self.btn_to_YNADSCV.pack(side=LEFT,padx=2)
        Label(f07, textvariable=self.ppdStr1,fg='#871F78',anchor="w").pack(side=LEFT,padx=2, pady=2,fill=X)



    def get_pp_count(self):
        while True:
            sqlstr='''select 
                        count(case when deepseek='高度匹配' and source_kw_type = '指定关联词' then 1 end),
                        count(case when deepseek='高度匹配' and source_kw_type = '指定关联词' and is_add=1 then 1 end),
                        count(case when deepseek='比较匹配' and source_kw_type = '指定关联词' then 1 end),
                        count(case when deepseek='比较匹配' and source_kw_type = '指定关联词' and is_add=1 then 1 end)
                        from [ShopeeeADTZKW]
                '''
            rs1=self.ms.ExecQuery(sqlstr)
            ttt=rs1[0]
            self.ppdStr1.set(f'在用广告出单词关联词,高度匹配总数:{ttt[0]},已添加为精准词:{ttt[1]};比较匹配总数:{ttt[2]},已添加为精准词:{ttt[3]}')
            time.sleep(10)
    def get_tt_count(self):

        while True:
            sqlstr='''select 
                        count(case when deepseek is not null and source_kw_type = '指定关联词' and kw!=source_kw then 1 end),
                        count(case when deepseek is null and source_kw_type = '指定关联词' and kw!=source_kw then 1 end),
                        count(case when is_cj_kw=1 and source_kw_type = '指定关联词' and kw=source_kw then 1 end),
                        count(case when is_cj_kw=0 and source_kw_type = '指定关联词' and kw=source_kw then 1 end)
                        from [ShopeeeADTZKW]
                '''
            rs1=self.ms.ExecQuery(sqlstr)
            ttt=rs1[0]
            self.ppdStr3.set(f'已采集:{ttt[0]},需采集:{ttt[1]}')
            self.GlStr1.set(f'已采集:{ttt[2]},需采集:{ttt[3]}')

            time.sleep(10)
    
    def get_task_state(self):
        while True:
            rs= self.ms.ExecQuery('select id,state,tcount from task where id in (44,45)')
            
            for tid, tstate,tcount in rs:
                cbtn=self.btn_start_task if tid==45 else self.btn_glcj_ppd
                if tstate==0:
                    if tid==45:
                        self.ppdStr2.set('任务未开启')
                    cbtn.config(state=NORMAL)
                else:
                    if tid==45:
                        self.ppdStr2.set(f'正在运行,店铺剩余需运行:{tcount}')
                    cbtn.config(state=DISABLED)
            time.sleep(10)


    def start_task(self):
        
        
        cbtn=self.btn_start_task
        cbtn.config(state=DISABLED)

        task_name='采集关联词'

        rs=self.ms.ExecQuery('select count(*) from houtai where isAddKw=1 and is_task_baned=0')

        aff= self.ms.ExecNoQuery('update task set state=1,tcount=? where id=45',(rs[0][0]))
        if aff>0:
            print(f'任务《{task_name}》启动成功,待采集数:{rs[0][0]}')
        else:
            print(f'任务《{task_name}》启动失败')

    
    def cj_ppd(self):

        cbtn=self.btn_glcj_ppd
        stype='关联词'
        task_name='采集关联词拓展词匹配度'
        task_id=44
        cbtn.config(state=DISABLED)

        
        rs=self.ms.ExecQuery(f"select count(*) from [ShopeeeADTZKW] where deepseek is null and source_kw_type = '{stype}' ")
        if rs[0][0]==0:
            print(f'当前表《ShopeeeADTZKW》{stype}待无匹配度待采集')
        else:

            aff= self.ms.ExecNoQuery('update task set state=1,tcount=? where id=?',(rs[0][0],task_id))
            if aff>0:
                print(f'任务《{task_name}》启动成功,待采集数:{rs[0][0]}')
            else:
                print(f'任务《{task_name}》启动失败')

    def to_ynadcsv(self):
       
        cbtn=self.btn_to_YNADSCV

        cbtn.config(state=DISABLED)

        sql_str=f'''
            select * from ShopeeeADTZKW as s left join campaign as c on s.ProductID=c.product_id
            where s.source_kw_type='关联词'
            and s.is_add=0
            and
            ( ((LEN(TRIM(s.source_kw)) - LEN(REPLACE(TRIM(s.source_kw), ' ', '')) + 1)=1 and s.deepseek='高度匹配' )
                or
                ((LEN(TRIM(s.source_kw)) - LEN(REPLACE(TRIM(s.source_kw), ' ', '')) + 1)>=2 and s.deepseek in ('比较匹配','高度匹配') )
            )
            '''
        rs=self.ms.ExecQuerydict(sql_str)
        print(f'共获取到 {len(rs)} 个可导入长尾词,开始导入...')
        cur_time=datetime.now()
        j=1
        suc=0
        fail=0
        ig_n=0
        for r in rs:

            kw_form='新拓展词表关联词'
            
            pid=r['ProductID']
            kw=r['kw']
            rs1=self.ms.ExecQuery('select count(*) from YN_AD_CSV where Product_ID=? and Search_Query=?',(pid,kw))

            if rs1[0][0]==0:

                pdatas={
                    'BrowserID':r['bid'],
                    'DpName':r['bname'],
                    'GroupName':r['gname'],
                    'User_Name':r['UserName'],
                    'Shop_ID':r['shopid'],
                    'Product_Name_Ad_Name':r['PTitle'],
                    'Product_ID':pid,
                    'Search_Query':kw,
                    'Search_Volume':r['SearchCount'],
                    'uploadTime':cur_time,
                    'updateTime':cur_time,
                    'WordNum':r['WordNum'],
                    'deepseek':r['deepseek'],
                    'kwfrom':kw_form
                }
                zdstr=','.join(pdatas.keys())
                params=list(pdatas.values())
                wstr=','.join(['?' for _ in params])
                aff=self.ms.ExecNoQuery(f'insert into YN_AD_CSV({zdstr}) values({wstr})',params)
                if aff>0:
                    suc+=1
                    aff2=self.ms.ExecNoQuery('update ShopeeeADTZKW set update_time=?,is_add=1 where ProductID=? and kw=?',(cur_time,pid,kw))
                    print(f'第{j}个拓展词:《{kw}》,添加到《YN_AD_CSV》精准词表成功,标记状态:{aff2},任务剩余:{len(rs)-j}')
                else:
                    fail+=1
                    print(f'第{j}个拓展词:《{kw}》,添加到《YN_AD_CSV》精准词表失败,任务剩余:{len(rs)-j}')
            else:
                ig_n+=1
                aff2=self.ms.ExecNoQuery('update ShopeeeADTZKW set update_time=?,is_add=1 where ProductID=? and kw=?',(cur_time,pid,kw))
                print(f'第{j}个拓展词:《{kw}》,《YN_AD_CSV》精准词表已存在该词忽略,标记状态:{aff2}任务剩余:{len(rs)-j}')

            j+=1

        print(f'本次需导入{len(rs)}个数据到,导入精准词表,其中成功:{suc},失败:{fail},已存在忽略:{ig_n}')
        messagebox.showinfo('提示',f'本次需导入{len(rs)}个数据到,导入精准词表,其中成功:{suc},失败:{fail},已存在忽略:{ig_n}')
        cbtn.config(state=NORMAL)


# import tkinter
# list_win=tkinter.Tk()
# list_win.wm_geometry("860x700")
# list_win.title('虾皮采集关联词功能')
# ShopeeADKW2Fra(list_win).pack(fill=X,padx=3,pady=3)
# list_win.mainloop()